drop table if exists dwd_fact_coupon_use;
create external table dwd_fact_coupon_use(
`id` string COMMENT '编号',
`coupon_id` string COMMENT '优惠券 ID',
`user_id` string COMMENT 'userid',
`order_id` string COMMENT '订单 id',
`coupon_status` string COMMENT '优惠券状态',
`get_time` string COMMENT '领取时间',
`using_time` string COMMENT '使用时间(下单)',
`used_time` string COMMENT '使用时间(支付)'
) COMMENT '优惠券领用事实表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
location '/warehouse/gmall/dwd/dwd_fact_coupon_use/';


set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_fact_coupon_use partition(dt)
select
if(new.id is null,old.id,new.id), -- string COMMENT '编号',
if(new.coupon_id is null,old.coupon_id,new.coupon_id), -- string COMMENT '编号',
if(new.user_id is null,old.user_id,new.user_id), -- string COMMENT '编号',
if(new.order_id is null,old.order_id,new.order_id), -- string COMMENT '编号',
if(new.coupon_status is null,old.coupon_status,new.coupon_status), -- string COMMENT '编号',
if(new.get_time is null,old.get_time,new.get_time), -- string COMMENT '编号',
if(new.using_time is null,old.using_time,new.using_time), -- string COMMENT '编号',
if(new.used_time is null,old.used_time,new.used_time), -- string COMMENT '编号',
date_format(if(new.get_time is null,old.get_time,new.get_time),'yyyy-MM-dd') -- string COMMENT '编号',
from
(
select
id,
coupon_id,
user_id,
order_id,
coupon_status,
get_time,
using_time,
used_time
from dwd_fact_coupon_use
where dt in
(
select
date_format(get_time,'yyyy-MM-dd')
from ods_coupon_use
where dt='2020-08-03'
)
)old
full outer join
(
select
id,
coupon_id,
user_id,
order_id,
coupon_status,
get_time,
using_time,
used_time
from ods_coupon_use
where dt='2020-08-03'
)new
on old.id=new.id;